const express = require('express');
const mysql = require("mysql2/promise");
const router = express.Router();
const axios = require('axios');
const {resolve} = require('path');
// 引入mqtt
const mqtt = require('mqtt');

// 定义主题
const topic1 = 'presences';
const topic2 = 'dashboard';
let connectState = false;  // 连接状态

// 设置微信小程序
let appid = 'wx2f41742babc57461';
let secret = '4dd14fb5a3c9606989abc2b0f53e12fe';
let grant_type = 'authorization_code';  // 授权类型

// mqtt配置
const client  = mqtt.connect('mqtt://49.235.159.177:1883', {
    clientId: '',
    clean: true,
    connectTimeout: 4000,
    username: '',
    password: '',
    reconnectPeriod: 1000,
})

// 连接检查
client.on("connect", () => {
    console.log('连接成功');
    connectState = true;
    client.subscribe(topic1, () => {
        console.log(`订阅的主题 ${topic1}`)
    });
    client.subscribe(topic2, () => {
        console.log(`订阅的主题 ${topic2}`)
    });
});

client.on("disconnect", () => {
    console.log("连接已断开");
    connectState = false;
});

// 收到的数据
client.on('message', async (topic, payload) => {
    let str = payload.toString();  // 接受的数据为Buffer类型 我们这个转换成字符串类型；
    try {  // 如果是成功的话
        console.log('收到的topic值', topic);  // 打印收到的数据
        if (topic === topic1){
            let json = JSON.parse(str); // 把字符串格式转换成JSON格式
            console.log('json:', json);  // 打印JSON格式的数据
            let conn = await getConn();  // 调用验证数据库的函数
            let id = json.device_id;  // 接受的id
            let checked = json.checked;  // 接受的状态
            let [results] = await conn.execute('UPDATE `devicelist` SET `checked` =? WHERE `device_id`=?', [Number(checked), String(id)]);  // 修改数据库的id 和 checked
        }else {
            if (topic === topic2){
                let json = JSON.parse(str); // 把字符串格式转换成JSON格式
                let conn = await getConn();  // 调用验证数据库的函数
                let id = json.id;  // 接受的id
                let name = json.name;  // 接受的名字
                let value = json.value;  // 接受的值
                let [results] = await conn.execute('UPDATE `dashboard` SET `name` =?, `value`=? WHERE `id`=?', [String(name), Number(value), Number(id)]);  // 修改数据库的id,name 和 value
            }
        }
    } catch (ex) { // 如果是错误
        console.error(ex);  // 错误提示
        console.log('not json', str);  // JSON 没有
    }
});


// 发布
function pushMsg(msg) {
    if (topic1) {
        client.publish(topic1, msg, (err) => {
            if (err) {
                console.error('发送失败', err);
            } else {
                console.log('发送成功');
            }
        })
    }else {
        client.publish(topic2, msg, (err) => {
            if (err) {
                console.error('发送失败', err);
            } else {
                console.log('发送成功');
            }
        })
    }
}

// 初始值
let connection;

// 验证数据库
async function getConn() {
    if (connection) return connection;
    connection = await mysql.createConnection({
        host: 'localhost',
        user: 'nodejs',
        database: 'nodejs',
        password: 't2019213282',
    });
    return connection;
}

// 获取当前时间
function getTime() {
    let year = new Date().getFullYear();
    let month = new Date().getMonth() + 1;
    let day = new Date().getDate();
    let hour = new Date().getHours();
    let minute = new Date().getMinutes();
    let second = new Date().getSeconds();
    let time = `${year}-${month}-${day} ${hour}:${minute}:${second}`;
    return time;
}


// web端设备列表
router.post('/api/web_device', async (req,res) =>{
    let conn = await getConn();
    // user_device表和devicelist表联表查询
    let [results] = await conn.query('SELECT * FROM `user_device` LEFT JOIN `devicelist` ON `user_device`.`device_id`=`devicelist`.`id`');
    res.json(results);
})


// 获取分类列表
router.post('/api/list', async (req, res) => {
    let conn = await getConn();

    let [results] = await conn.query('SELECT * FROM `list`');
    res.json(results);
})

// 获取设备列表
router.post('/api/devicelist', async (req, res) => {
    let conn = await getConn();
    // console.log(req.query);
    let user_id = req.query.user_id;
    // MySQL联表查询
    let [results] = await conn.query('SELECT * FROM `devicelist` WHERE `id` IN (SELECT `device_id` FROM `user_device` WHERE `user_id`=(SELECT `id` FROM `user` WHERE `user_id`=?))', [user_id]);
    res.json(results);
})


// 获取仪表盘数据
router.post('/api/dashboard', async (req, res) => {
    let conn = await getConn();
    let [results] = await conn.query('SELECT * FROM `dashboard`');
    res.json(results);
})

// 用户登录接口
router.post('/api/login', async (req, res) => {
    let conn = await getConn();
    let user_code = req.query.user_code;
    if (!user_code) {
        res.json({code: 400, msg: '参数错误'});
        return;
    }else{
        try {
            let response = await axios.get(`https://api.weixin.qq.com/sns/jscode2session?appid=${appid}&secret=${secret}&js_code=${user_code}&grant_type=${grant_type}`);
            // console.log('response:', response.data);
            let user_id = response.data.openid;
            console.log('user_id:', user_id);
            let date_time = getTime();
            console.log('date_time:', date_time);
            // 查询数据库存储的用户
            let [user] = await conn.query('SELECT * FROM `user` WHERE `user_id`=?', [user_id]);
            // console.log('user:', user);
            if (user.length > 0) {
                res.json(user_id);
                // console.log('用户已存在');
            }else {
                let [results] = await conn.execute('INSERT INTO `user`(`user_id`, `create_time`) VALUES(?,?)', [user_id, date_time]);
                res.json(user_id);
                // console.log('用户添加成功');
            }
        } catch (error) {
            console.log("error:", error);
        }
    }
})

// 用户查询
router.post('/api/user', async (req, res) => {
    let conn = await getConn();
    let user_id = req.query.user_id;
    // 查询数据库存储的用户
    let [user] = await conn.query('SELECT * FROM `user` WHERE `user_id`=?', [user_id]);
    // console.log('user:', user);
    res.json(user);
})

// add 接口
router.post('/api/device_add', async (req, res) => {
    let conn = await getConn();
    let data = JSON.parse(req.query.device);
    let user_id = req.query.user_id;
    let device_id = data.device_id;
    let date_time = getTime();
    // MySQL联表查询在用户的设备表中查询设备的id
    let [results] = await conn.query('SELECT * FROM `user_device` WHERE `device_id`=(SELECT `id` FROM `devicelist` WHERE `device_id`=?)', [device_id]);
    if (results.length > 0) {
        let msg = {code: 400, msg: '设备已存在'};
        res.json(msg);
    }else{
        // 查询数据库存储的用户
        let [user] = await conn.query('SELECT * FROM `user` WHERE `user_id`=?', [user_id]);
        // 查询数据库存储的设备
        let [device] = await conn.query('SELECT * FROM `devicelist` WHERE `device_id`=?', [device_id]);
        // 添加在用户的设备表
        let [user_device] = await conn.execute('INSERT INTO `user_device`(`user_id`, `device_id`, `room_id`, `create_time`) VALUES(?,?,?,?)', [user[0].id, device[0].id, device[0].type, date_time]);
        res.json(user_device);    // 返回结果
    }
})



// 更新接口
router.post('/api/update', async (req, res) => {
    let conn = await getConn();
    let id = req.query.id;
    let checked = req.query.checked;
    let [results] = await conn.execute('UPDATE `devicelist` SET `checked` =? WHERE `device_id`=?', [checked, id]);
    pushMsg(JSON.stringify({device_id:id, checked}));

    res.json(results);
    // console.log(req.query);
})

// 获取html文件
router.get('/', async (req, res) => {
    try {
        res.sendFile(resolve('./public/index.html'));    // 将标题作为响应发送给客户端
    } catch (error) {
        console.log("error:", error);
        res.status(500).send('An error occurred')
    }
})

//导出路由
module.exports= router;